﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;
using System.Data;
using System.Configuration;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingCoursesDAL : ITrainingCoursesDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

       public IList<TrainingCoursesInfo> GetFull()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<TrainingCoursesInfo> infoList = new List<TrainingCoursesInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(1, "@train_seq", train_seq);
                
                strSQL = "SELECT tc.ref_no"
                    + " , tc.training_course_descr"
                    + " , tc.date_sign_start"
                    + " , tc.date_sign_end"
                    + " , tc.date_training_start"
                    + " , tc.date_training_end"
                    + " , tc.time_sign_end"
                    + " , tc.schedule_text"
                    + " , tc.max_trainee_count"
                    + " , tc.unit_price"
                    + " , tc.total_score"
                    + " , tc.score_very_good"
                    + " , tc.score_good"
                    + " , tc.score_acceptable"
                    + " , tc.score_type"
                    + " , tc.emp_sec_seq"
                    + " , tc.closed"
                    + " , tc.epc_seq"
                    + " , tc.training_group_seq"
                    + " , tc.unit_price"
                    + " , tc.total_cost"
                    + " , tc.bg_source_seq"
                    + " , tc.org_text"
                    + " , tc.training_cur_seq"
                    + " , cu.training_cur_descr"
                    + " , tc.total_hours"
                    + " , tc.training_ins_seq"
                    + " , tc.schedule_text"
                    + " , sf.scholar_fund_seq"
                    + " , tc.total_cost"
                    + " , tc.remarks"
                    + " , mc.country_code"
                    + " , tc.country_seq"
                    + " , ti.training_ins_descr"
                    + " , sf.scholar_fund_descr"
                    + " , tg.training_group_descr"
                    + " , ms.bgm_source_name"
                    + " , tc.train_seq"
                    + " , cu.training_cur_descr"
                    + " , tc.total_price"
                    + " FROM hrm_training_courses tc"
                    + " Left Join mas_countries mc ON tc.country_seq = mc.country_seq"
                    + " Left Join hrm_training_institutes ti ON tc.training_ins_seq = ti.training_ins_seq"
                    + " AND ti.record_status not like @delete_status"
                    + " Left Join hrm_scholarship_funds sf ON tc.scholar_fund_seq = sf.scholar_fund_seq"
                    + " Left Join hrm_training_groups tg ON tc.training_group_seq = tg.training_group_seq"
                    + " Left Join hrm_training_curriculums cu ON tc.training_cur_seq = cu.training_cur_seq"
                    + " Left Join bgm_mas_source ms ON tc.bg_source_seq = ms.bgm_source_seq"
                    + " WHERE tc.record_status not like @delete_status"
                    ;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {

                    for (int i = 0; i < drc.Count; i++)
                    {
                        TrainingCoursesInfo info = new TrainingCoursesInfo();
                        info.train_seq = Convert.ToInt32(drc[i]["train_seq"]);
                        info.training_ins_seq = cn.ConvertNullInt(drc[i]["training_ins_seq"]);
                        info.training_cur_seq = cn.ConvertNullInt(drc[i]["training_cur_seq"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.training_course_descr = drc[i]["training_course_descr"].ToString();
                        info.schedule_text = drc[i]["schedule_text"].ToString();
                        info.scholar_fund_seq = cn.ConvertNullInt(drc[i]["scholar_fund_seq"]);
                        info.bg_source_seq = cn.ConvertNullInt(drc[i]["bg_source_seq"]);
                        info.training_group_seq = cn.ConvertNullInt(drc[i]["training_group_seq"]);
                        info.emp_sec_seq = cn.ConvertNullInt(drc[i]["emp_sec_seq"]);
                        info.date_sign_start = cn.ConvertNullDateTime(drc[i]["date_sign_start"]);
                        info.date_sign_end = cn.ConvertNullDateTime(drc[i]["date_sign_end"]);
                        info.date_training_start = cn.ConvertNullDateTime(drc[i]["date_training_start"]);
                        info.date_training_end = cn.ConvertNullDateTime(drc[i]["date_training_end"]);
                        info.time_sign_end = drc[i]["time_sign_end"].ToString();
                        info.max_trainee_count = cn.ConvertNullInt(drc[i]["max_trainee_count"]);
                        info.total_cost = cn.ConvertNullInt(drc[i]["total_cost"]);
                        info.schedule_text = drc[i]["schedule_text"].ToString();
                        info.country_seq = cn.ConvertNullInt(drc[i]["country_seq"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        //info.record_status = drc[i]["record_status"].ToString();
                        info.Training_cur_descr = drc[i]["training_cur_descr"].ToString();
                        info.total_score = cn.ConvertNullInt(drc[i]["total_score"]);
                        info.score_very_good = cn.ConvertNullInt(drc[i]["score_very_good"]);
                        info.score_acceptable = cn.ConvertNullInt(drc[i]["score_acceptable"]);
                        info.score_type = cn.ConvertNullInt(drc[i]["score_type"]);
                        info.total_price = cn.ConvertToDecimal(drc[i]["total_price"]);
                        
                        infoList.Add(info);

                      }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
       public DataSet GetData()
       {
           DataSet ds = new DataSet();
           dbManager.Open();
           dbManager.CreateParameters(1);

           dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

           string sql = "SELECT tc.ref_no"
                    + " , tc.training_course_descr"
                    + " , tc.date_sign_start"
                    + " , tc.date_sign_end"
                    + " , tc.date_training_start"
                    + " , tc.date_training_end"
                    + " , tc.time_sign_end"
                    + " , tc.schedule_text"
                    + " , tc.max_trainee_count"
                    + " , tc.unit_price"
                    + " , tc.total_score"
                    + " , tc.score_very_good"
                    + " , tc.score_good"
                    + " , tc.score_acceptable"
                    + " , tc.score_type"
                    + " , tc.emp_sec_seq"
                    + " , tc.closed"
                    + " , tc.epc_seq"
                    + " , tc.training_group_seq"
                    + " , tc.unit_price"
                    + " , tc.total_cost"
                    + " , tc.bg_source_seq"
                    + " , tc.org_text"
                    + " , tc.training_cur_seq"
                    + " , cu.training_cur_descr"
                    + " , tc.total_hours"
                    + " , tc.training_ins_seq"
                    + " , tc.schedule_text"
                    + " , sf.scholar_fund_seq"
                    + " , tc.total_cost"
                    + " , tc.remarks"
                    + " , mc.country_code"
                    + " , tc.country_seq"
                    + " , ti.training_ins_descr"
                    + " , sf.scholar_fund_descr"
                    + " , tg.training_group_descr"
                    + " , ms.bgm_source_name"
                    + " , tc.train_seq"
                    + " , cu.training_cur_descr"
                    + " , tc.total_price"
                    + " , (select count(trainee_seq)  from hrm_training_courses_trainees tt where tt.train_seq = tc.train_seq and tt.record_status not like @delete_status) AS COUNT_TRAINEE"
                    + " , (select count(trainee_seq) from hrm_training_courses_trainees  tt where tt.train_seq = tc.train_seq and granted = 1) AS COUNT_TRAINEE_GRANTED"
                    + " FROM hrm_training_courses tc"
                    + " Left Join mas_countries mc ON tc.country_seq = mc.country_seq"
                    + " Left Join hrm_training_institutes ti ON tc.training_ins_seq = ti.training_ins_seq"
                    + " Left Join hrm_scholarship_funds sf ON tc.scholar_fund_seq = sf.scholar_fund_seq"
                    + " Left Join hrm_training_groups tg ON tc.training_group_seq = tg.training_group_seq"
                    + " Left Join hrm_training_curriculums cu ON tc.training_cur_seq = cu.training_cur_seq"
                    + " Left Join bgm_mas_source ms ON tc.bg_source_seq = ms.bgm_source_seq"
                    + " WHERE tc.record_status not like @delete_status"
                    + " AND ti.record_status not like @delete_status";

           ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
           dbManager.Dispose();
           return ds;

       }
       public TrainingCoursesInfo GetData(int train_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            TrainingCoursesInfo info = new TrainingCoursesInfo();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@train_seq", train_seq);

                strSQL = "SELECT tc.ref_no"
                    + " , tc.training_course_descr"
                    + " , tc.date_sign_start"
                    + " , tc.date_sign_end"
                    + " , tc.date_training_start"
                    + " , tc.date_training_end"
                    + " , tc.time_sign_end"
                    + " , tc.schedule_text"
                    + " , tc.max_trainee_count"
                    + " , tc.unit_price"
                    + " , tc.total_cost"
                    + " , tc.total_score"
                    + " , tc.score_very_good"
                    + " , tc.score_good"
                    + " , tc.score_acceptable"
                    + " , tc.score_type"
                    + " , tc.emp_sec_seq"
                    + " , tc.closed"
                    + " , tc.epc_seq"
                    + " , tc.training_group_seq"
                    + " , tc.total_price"
                    + " , tc.bg_source_seq"
                    + " , tc.org_text"
                    + " , tc.training_cur_seq"
                    + " , tc.total_hours"
                    + " , tc.training_ins_seq"
                    + " , sf.scholar_fund_seq"
                    + " , tc.total_cost"
                    + " , tc.remarks"
                    + " , mc.country_code"
                    + " , tc.country_seq"
                    + " , ti.training_ins_descr"
                    + " , sf.scholar_fund_descr"
                    + " , tg.training_group_descr"
                    + " , ms.bgm_source_name"
                    + " , tc.train_seq"
                    + " , cu.training_cur_descr"
                    + " FROM hrm_training_courses tc"
                    + " Left Join mas_countries mc ON tc.country_seq = mc.country_seq"
                    + " Left Join hrm_training_institutes ti ON tc.training_ins_seq = ti.training_ins_seq"
                    + " Left Join hrm_scholarship_funds sf ON tc.scholar_fund_seq = sf.scholar_fund_seq"
                    + " Left Join hrm_training_groups tg ON tc.training_group_seq = tg.training_group_seq"
                    + " Left Join hrm_training_curriculums cu ON tc.training_cur_seq = cu.training_cur_seq"
                    + " Left Join bgm_mas_source ms ON tc.bg_source_seq = ms.bgm_source_seq"
                    + " WHERE tc.record_status not like @delete_status"
                    + (train_seq > 0 ? " AND tc.train_seq = @train_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.train_seq = Convert.ToInt32(drc[0]["train_seq"]);
                    info.training_ins_seq =  cn.ConvertNullInt(drc[0]["training_ins_seq"]);
                    info.training_cur_seq = cn.ConvertNullInt(drc[0]["training_cur_seq"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.training_course_descr = drc[0]["training_course_descr"].ToString();
                    info.training_ins_descr = drc[0]["training_ins_descr"].ToString();
                    info.scholar_fund_seq = cn.ConvertNullInt(drc[0]["scholar_fund_seq"]);
                    info.bg_source_seq = cn.ConvertNullInt(drc[0]["bg_source_seq"]);
                    info.training_group_seq = cn.ConvertNullInt(drc[0]["training_group_seq"]);
                    info.emp_sec_seq = cn.ConvertNullInt(drc[0]["emp_sec_seq"]);
                    info.date_sign_start = cn.ConvertNullDateTime(drc[0]["date_sign_start"]);
                    info.date_sign_end = cn.ConvertNullDateTime(drc[0]["date_sign_end"]);
                    info.date_training_start = cn.ConvertNullDateTime(drc[0]["date_training_start"]);
                    info.date_training_end = cn.ConvertNullDateTime(drc[0]["date_training_end"]);
                    info.time_sign_end = drc[0]["time_sign_end"].ToString();
                    info.max_trainee_count = cn.ConvertNullInt(drc[0]["max_trainee_count"]);
                    //info.total_price = cn.ConvertNullInt(drc[0]["total_cost"]);
                    info.total_cost = cn.ConvertNullInt(drc[0]["total_cost"]);
                    info.schedule_text = drc[0]["schedule_text"].ToString();
                    info.country_seq = cn.ConvertNullInt(drc[0]["country_seq"]);
                    info.remarks = drc[0]["remarks"].ToString();
                    //info.record_status = drc[0]["record_status"].ToString();
                    info.total_score = cn.ConvertNullInt(drc[0]["total_score"]);
                    info.score_very_good = cn.ConvertNullInt(drc[0]["score_very_good"]);
                    info.score_acceptable = cn.ConvertNullInt(drc[0]["score_acceptable"]);
                    info.score_type = cn.ConvertNullInt(drc[0]["score_type"]);
                    info.total_price = cn.ConvertToDecimal(drc[0]["total_price"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        public void Insert(TrainingCoursesInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(28);

                dbManager.AddParameters(0, "@ref_no", info.ref_no);
                dbManager.AddParameters(1, "@training_course_descr", info.training_course_descr);
                dbManager.AddParameters(2, "@training_cur_seq", info.training_cur_seq);
                dbManager.AddParameters(3, "@date_sign_start", info.date_sign_start);
                dbManager.AddParameters(4, "@date_sign_end", info.date_sign_end);
                dbManager.AddParameters(5, "@date_training_start", info.date_training_start);
                dbManager.AddParameters(6, "@date_training_end", info.date_training_end);
                dbManager.AddParameters(7, "@schedule_text", info.schedule_text);
                dbManager.AddParameters(8, "@max_trainee_count", info.max_trainee_count);
                dbManager.AddParameters(9, "@total_cost", info.total_cost);
                dbManager.AddParameters(10, "@total_score", info.total_score);
                dbManager.AddParameters(11, "@score_very_good", info.score_very_good);
                dbManager.AddParameters(12, "@score_good", info.score_good);
                dbManager.AddParameters(13, "@score_acceptable", info.score_acceptable);
                dbManager.AddParameters(14, "@score_type", info.score_type);
                dbManager.AddParameters(15, "@closed", info.closed);
                dbManager.AddParameters(16, "@scholar_fund_seq", info.scholar_fund_seq);
                dbManager.AddParameters(17, "@epc_seq", info.epc_seq);
                dbManager.AddParameters(18, "@training_ins_seq", info.training_ins_seq);
                dbManager.AddParameters(19, "@remarks", info.remarks);
                dbManager.AddParameters(20, "@country_seq", info.country_seq);
                dbManager.AddParameters(21, "@training_group_seq", info.training_group_seq);
                dbManager.AddParameters(22, "@bg_source_seq", info.bg_source_seq);
                dbManager.AddParameters(23, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(24, "@created_by", info.created_by);
                dbManager.AddParameters(25, "@created_when", info.created_when);
                dbManager.AddParameters(26, "@time_sign_end", info.time_sign_end);
                dbManager.AddParameters(27, "@total_price", cn.ConvertToDecimal(info.total_price));


                strSQL = "INSERT INTO hrm_training_courses"
                    + " (ref_no"
                    + ", training_course_descr"
                    + ", training_cur_seq"
                    + ", date_sign_start"
                    + ", date_sign_end"
                    + ", date_training_start"
                    + ", date_training_end"
                    + ", time_sign_end"
                    + ", schedule_text"
                    + ", max_trainee_count"
                    + ", total_cost"
                    + ", total_score"
                    + ", score_very_good"
                    + ", score_good"
                    + ", score_acceptable"
                    + ", score_type"
                    + ", closed"
                    + ", scholar_fund_seq"
                    + ", epc_seq"
                    + ", training_ins_seq"
                    + ", remarks"
                    + ", country_seq"
                    + ", training_group_seq"
                    + ", bg_source_seq"
                    + ", record_status"
                    + ", created_by"
                    + ", total_price"
                    + ", created_when)"
                    + " VALUES (@ref_no"
                    + ", @training_course_descr"
                    + ", @training_cur_seq"
                    + ", @date_sign_start"
                    + ", @date_sign_end"
                    + ", @date_training_start"
                    + ", @date_training_end"
                    + ", @time_sign_end"
                    + ", @schedule_text"
                    + ", @max_trainee_count"
                    + ", @total_cost"
                    + ", @total_score"
                    + ", @score_very_good"
                    + ", @score_good"
                    + ", @score_acceptable"
                    + ", @score_type"
                    + ", @closed"
                    + ", @scholar_fund_seq"
                    + ", @epc_seq"
                    + ", @training_ins_seq"
                    + ", @remarks"
                    + ", @country_seq"
                    + ", @training_group_seq"
                    + ", @bg_source_seq"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @total_price"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update(TrainingCoursesInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(28);

                dbManager.AddParameters(0, "@ref_no", info.ref_no);
                dbManager.AddParameters(1, "@training_course_descr", info.training_course_descr);
                dbManager.AddParameters(2, "@training_cur_seq", info.training_cur_seq);
                dbManager.AddParameters(3, "@date_sign_start", info.date_sign_start);
                dbManager.AddParameters(4, "@date_sign_end", info.date_sign_end);
                dbManager.AddParameters(5, "@date_training_start", info.date_training_start);
                dbManager.AddParameters(6, "@date_training_end", info.date_training_end);
                dbManager.AddParameters(7, "@schedule_text", info.schedule_text);
                dbManager.AddParameters(8, "@max_trainee_count", info.max_trainee_count);
                dbManager.AddParameters(9, "@total_cost", info.total_cost);
                dbManager.AddParameters(10, "@total_score", info.total_score);
                dbManager.AddParameters(11, "@score_very_good", info.score_very_good);
                dbManager.AddParameters(12, "@score_good", info.score_good);
                dbManager.AddParameters(13, "@score_acceptable", info.score_acceptable);
                dbManager.AddParameters(14, "@score_type", info.score_type);
                dbManager.AddParameters(15, "@closed", info.closed);
                dbManager.AddParameters(16, "@scholar_fund_seq", info.scholar_fund_seq);
                dbManager.AddParameters(17, "@epc_seq", info.epc_seq);
                dbManager.AddParameters(18, "@training_ins_seq", info.training_ins_seq);
                dbManager.AddParameters(19, "@remarks", info.remarks);
                dbManager.AddParameters(20, "@country_seq", info.country_seq);
                dbManager.AddParameters(21, "@training_group_seq", info.training_group_seq);
                dbManager.AddParameters(22, "@bg_source_seq", info.bg_source_seq);
                dbManager.AddParameters(23, "@train_seq", info.train_seq);
                dbManager.AddParameters(24, "@updated_by", info.updated_by);
                dbManager.AddParameters(25, "@updated_when", info.updated_when);
                dbManager.AddParameters(26, "@time_sign_end", cn.ConvertNullDateTime(info.time_sign_end));
                dbManager.AddParameters(27, "@total_price", cn.ConvertToDecimal(info.total_price));


                strSQL = "UPDATE hrm_training_courses"
                    + " SET ref_no = @ref_no"
                    + ", training_course_descr = @training_course_descr"
                    + ", training_cur_seq = @training_cur_seq"
                    + ", date_sign_start = @date_sign_start"
                    + ", date_sign_end = @date_sign_end"
                    + ", date_training_start = @date_training_start"
                    + ", date_training_end = @date_training_end"
                    + ", time_sign_end = @time_sign_end"
                    + ", schedule_text = @schedule_text"
                    + ", max_trainee_count = @max_trainee_count"
                    + ", total_cost = @total_cost"
                    + ", total_score = @total_score"
                    + ", score_very_good = @score_very_good"
                    + ", score_good = @score_good"
                    + ", score_acceptable = @score_acceptable"
                    + ", score_type = @score_type"
                    + ", closed = @closed"
                    + ", scholar_fund_seq = @scholar_fund_seq"
                    + ", epc_seq = @epc_seq"
                    + ", training_ins_seq = @training_ins_seq"
                    + ", remarks = @remarks"
                    + ", country_seq = @country_seq"
                    + ", training_group_seq = @training_group_seq"
                    + ", bg_source_seq = @bg_source_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + ", total_price = @total_price"
                    + " WHERE train_seq = @train_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(TrainingCoursesInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@train_seq", info.train_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE hrm_training_courses"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE train_seq = @train_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool chkDupTrainingCourse(string ref_no)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@ref_no", ref_no);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                string sql;
                sql = "SELECT tc.ref_no FROM hrm_training_courses tc"
                    + " WHERE tc.record_status not like @record_status"
                    + " AND tc.ref_no = @ref_no";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public bool chkDupTrainingQuta(string train_seq, string job_site_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@train_seq", Convert.ToInt32(train_seq));
                dbManager.AddParameters(1, "@job_site_seq", Convert.ToInt32(job_site_seq));
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = "SELECT tcq.train_seq, tcq.job_site_seq"
                    + " FROM hrm_training_courses_quota tcq"
                    + " WHERE tcq.train_seq =  @train_seq"
                    + " AND tcq.job_site_seq =  @job_site_seq"
                    + " AND tcq.record_status =  @record_status";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
